﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OracleClient;
using System.Configuration;
using System.Data;
using System.IO;
using QASampling.Util;

namespace QASampling.BusinessObject
{
    public class DAO
    {
        private static readonly string URI_PREFIX = "file:///";
        private static readonly string URI_PATH_SEPARATOR = "/";
        private static readonly string ABSOLUTE_PATH_SEPARATOR = "\\";
        private static string configFile = System.Reflection.Assembly.GetExecutingAssembly().CodeBase.ToString() + ".config";

        internal static IList<QAJob> GetAllJob()
        {
            throw new NotImplementedException();
        }

        internal static IList<QAJob> GetJob(string username)
        {
            using (OracleConnection con = new OracleConnection(Decrypt(ConfigurationManager.ConnectionStrings["QA"].ConnectionString)))
            {
                con.Open();
                string sql = @"SELECT DOCID, NAMAPTKP, NPWP, MASAPAJAK, WAKTUPENUGASAN, DITUGASKANKEPADA, 
                            STATUSASAL, STATUSAKHIR, WAKTUPENGECEKAN, CATATAN, JENISSPT, BARCODE FROM QA_JOB
                            WHERE DITUGASKANKEPADA=:LOGINID AND (UPPER(STATUSAKHIR) = :STATUSAKHIR OR STATUSAKHIR IS NULL)";

                using (OracleCommand cmd = new OracleCommand(sql, con))
                {
                    cmd.Parameters.AddWithValue(":LOGINID", username);
                    cmd.Parameters.AddWithValue(":STATUSAKHIR", StatusSPT.Hold.ToString().ToUpper());
                    OracleDataReader reader = cmd.ExecuteReader();

                    List<QAJob> retval = new List<QAJob>();
                    while (reader.Read())
                    {
                        QAJob job = CreateQAJobObject(reader);

                        retval.Add(job);
                    }

                    return retval;
                }
            }
        }

        internal static QAJob GetLastJob(string username)
        {
            using (OracleConnection con = new OracleConnection(Decrypt(ConfigurationManager.ConnectionStrings["QA"].ConnectionString)))
            {
                con.Open();
                string sql = @"SELECT * FROM ( SELECT DOCID, NAMAPTKP, NPWP, MASAPAJAK, WAKTUPENUGASAN, DITUGASKANKEPADA, 
                            STATUSASAL, STATUSAKHIR, WAKTUPENGECEKAN, CATATAN, JENISSPT, BARCODE, row_number() over (order by WAKTUPENGECEKAN DESC) r 
                            FROM QA_JOB WHERE (to_char(WAKTUPENGECEKAN, 'mm/dd/yyyy') = :WAKTUPENGECEKAN) 
                            AND WAKTUPENGECEKAN IS NOT NULL AND DITUGASKANKEPADA=:LOGINID AND (STATUSAKHIR!=:STATUSAKHIR1 AND STATUSAKHIR!=:STATUSAKHIR2))
                            WHERE r = 1";

                using (OracleCommand cmd = new OracleCommand(sql, con))
                {
                    cmd.Parameters.AddWithValue(":WAKTUPENGECEKAN", DateTime.Now.ToString("MM/dd/yyyy"));
                    cmd.Parameters.AddWithValue(":LOGINID", username);
                    cmd.Parameters.AddWithValue(":STATUSAKHIR1", QAStatus.suspend.ToString());
                    cmd.Parameters.AddWithValue(":STATUSAKHIR2", QAStatus.pass.ToString());
                    OracleDataReader reader = cmd.ExecuteReader();

                    QAJob job = null;
                    while (reader.Read())
                    {
                        job = CreateQAJobObject(reader);
                    }

                    return job;
                }
            }
        }

        private static QAJob CreateQAJobObject(OracleDataReader reader)
        {
            QAJob job = new QAJob();
            job.DocId = reader["DOCID"].ToString();
            job.NamaPTKP = reader["NAMAPTKP"].ToString();
            job.Npwp = reader["NPWP"].ToString();
            job.MasaPajak = reader["MASAPAJAK"].ToString();
            job.WaktuPenugasan = (DateTime)reader["WAKTUPENUGASAN"];
            job.DitugaskanKepada = reader["DITUGASKANKEPADA"].ToString();
            job.StatusAsal = reader["STATUSASAL"].ToString();
            job.StatusAkhir = reader["STATUSAKHIR"] is DBNull ? "" : reader["STATUSAKHIR"].ToString();
            job.WaktuPengecekan = reader["WAKTUPENGECEKAN"] is DBNull ? DateTime.MaxValue : (DateTime)reader["WAKTUPENGECEKAN"];
            job.Catatan = reader["CATATAN"] is DBNull ? "" : reader["CATATAN"].ToString();
            job.JenisSPT = reader["JENISSPT"] is DBNull ? "" : reader["JENISSPT"].ToString();
            job.Barcode = reader["BARCODE"] is DBNull ? "" : reader["BARCODE"].ToString();
            return job;
        }

        private static int ExecuteNonQuery(string sqlStatement)
        {
            return ExecuteNonQuery(sqlStatement, null);
        }

        private static int ExecuteNonQuery(string sqlStatement, OracleParameter[] parameters)
        {
            using (OracleConnection con = new OracleConnection(Decrypt(ConfigurationManager.ConnectionStrings["Datacap"].ConnectionString)))
            {
                con.Open();
                using (OracleCommand cmd = new OracleCommand(sqlStatement, con))
                {
                    foreach (OracleParameter p in parameters)
                        cmd.Parameters.Add(p);

                    return cmd.ExecuteNonQuery();
                }
            }
        }

        internal static Form1111DM GetForm1111DM(string docId)
        {
            using (OracleConnection con = new OracleConnection(Decrypt(ConfigurationManager.ConnectionStrings["Datacap"].ConnectionString)))
            {
                con.Open();

                Form1111DM form = GetMainPage(docId, con);
                if (form == null) return form;

                Fill_A_Page(form, con);
                Fill_R_Page(form, con);

                return form;
            }
        }

        private static void Fill_R_Page(Form1111DM form, OracleConnection con)
        {
            string sql = @"SELECT
                              DOCID,
                              NAMAPEMBELI_R_11,
                              NPWP_R_11,
                              DPP_R_11,
                              PPN_R_11,
                              PPNBM_R_11,
                              ID
                            FROM DMR_BKPJKP_DN_11
                            WHERE DOCID=:DOCID";

            using (OracleCommand cmd = new OracleCommand(sql, con))
            {
                cmd.Parameters.AddWithValue(":DOCID", form.DocId);
                OracleDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    Form1111DM_Item i = new Form1111DM_Item();
                    i.DocId = reader["DOCID"] is DBNull ? String.Empty : reader["DOCID"].ToString();
                    i.Nama = reader["NAMAPEMBELI_R_11"] is DBNull ? String.Empty : reader["NAMAPEMBELI_R_11"].ToString();
                    i.Npwp = reader["NPWP_R_11"] is DBNull ? String.Empty : reader["NPWP_R_11"].ToString();
                    i.Dpp = reader["DPP_R_11"] is DBNull ? String.Empty : reader["DPP_R_11"].ToString();
                    i.Ppn = reader["PPN_R_11"] is DBNull ? String.Empty : reader["PPN_R_11"].ToString();
                    i.PPnBM = reader["PPNBM_R_11"] is DBNull ? String.Empty : reader["PPNBM_R_11"].ToString();
                    i.Id = reader["ID"] is DBNull ? String.Empty : reader["ID"].ToString();

                    form.R_Page.Items.Add(i);
                }
            }
        }

        private static void Fill_A_Page(Form1111DM form, OracleConnection con)
        {
            string sql = @"SELECT
                              DOCID,
                              NAMAPEMBELI_A_11,
                              NPWP_A_11,
                              DPP_A_11,
                              PPN_A_11,
                              PPNBM_A_11,
                              ID
                            FROM DMA_PDN_11
                            WHERE DOCID=:DOCID";

            using (OracleCommand cmd = new OracleCommand(sql, con))
            {
                cmd.Parameters.AddWithValue(":DOCID", form.DocId);
                OracleDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    Form1111DM_Item i = new Form1111DM_Item();
                    i.DocId = reader["DOCID"] is DBNull ? String.Empty : reader["DOCID"].ToString();
                    i.Nama = reader["NAMAPEMBELI_A_11"] is DBNull ? String.Empty : reader["NAMAPEMBELI_A_11"].ToString();
                    i.Npwp = reader["NPWP_A_11"] is DBNull ? String.Empty : reader["NPWP_A_11"].ToString();
                    i.Dpp = reader["DPP_A_11"] is DBNull ? String.Empty : reader["DPP_A_11"].ToString();
                    i.Ppn = reader["PPN_A_11"] is DBNull ? String.Empty : reader["PPN_A_11"].ToString();
                    i.PPnBM = reader["PPNBM_A_11"] is DBNull ? String.Empty : reader["PPNBM_A_11"].ToString();
                    i.Id = reader["ID"] is DBNull ? String.Empty : reader["ID"].ToString();

                    form.A_Page.Items.Add(i);
                }
            }
        }

        private static Form1111DM GetMainPage(string docId, OracleConnection con)
        {
            string sql = @"SELECT
                                  DOCID,
                                  DM_NAMAPKP_11,
                                  DM_ALAMAT_11,
                                  DM_NPWP_11,
                                  DM_MASA1_11,
                                  DM_MASA2_11,
                                  DM_TAHUN_11,
                                  DM_TH_BK1_11,
                                  DM_TH_BK2_11,
                                  DM_IA_DPP_11,
                                  DM_IB_DPP_11,
                                  DM_IC_DPP_11,
                                  DM_IIA1_PPN_11,
                                  DM_IIA2_PPN_11,
                                  DM_IIA3_PPN_11,
                                  DM_IIB_PPN_11,
                                  DM_IIC1_PRCN_11,
                                  DM_IIC1_PPN_11,
                                  DM_IIC2_PRCN_11,
                                  DM_IIC2_PPN_11,
                                  DM_IIC3_PPN_11,
                                  DM_IID1_PPN_11,
                                  DM_IID2_TGL_11,
                                  DM_IID2_PPN_11,
                                  DM_IID3_PPN_11,
                                  DM_IIE_PPN_11,
                                  DM_IIF_PPN_11,
                                  DM_IIG_PPN_11,
                                  DM_IIIA_11,
                                  DM_IIIB_11,
                                  DM_IVA_11,
                                  DM_IVB_11,
                                  DM_IVC_11,
                                  DM_IVD_11,
                                  DM_IVE_11,
                                  DMA_SUB_JML_DPP_11,
                                  DMA_SUB_JML_PPN_11,
                                  DMA_SUB_JML_PPNBM_11,
                                  DMR_SUB_JML_DPP_11,
                                  DMR_SUB_JML_PPN_11,
                                  DMR_SUB_JML_PPNBM_11
                                 FROM DMINDEX_11
                                 WHERE DOCID=:DOCID";

            using (OracleCommand cmd = new OracleCommand(sql, con))
            {
                cmd.Parameters.AddWithValue(":DOCID", docId);
                OracleDataReader reader = cmd.ExecuteReader();

                if (reader.Read())
                {
                    Form1111DM f = new Form1111DM();
                    f.DocId = reader["DOCID"] is DBNull ? String.Empty : reader["DOCID"].ToString();
                    f.NamaPtkp = reader["DM_NAMAPKP_11"] is DBNull ? String.Empty : reader["DM_NAMAPKP_11"].ToString();
                    f.Alamat = reader["DM_ALAMAT_11"] is DBNull ? String.Empty : reader["DM_ALAMAT_11"].ToString();
                    f.Npwp = reader["DM_NPWP_11"] is DBNull ? String.Empty : reader["DM_NPWP_11"].ToString();
                    string masa1 = reader["DM_MASA1_11"] is DBNull ? String.Empty : reader["DM_MASA1_11"].ToString();
                    string masa2 = reader["DM_MASA2_11"] is DBNull ? String.Empty : reader["DM_MASA2_11"].ToString();
                    string tahun = reader["DM_TAHUN_11"] is DBNull ? String.Empty : reader["DM_TAHUN_11"].ToString();
                    string tahunBuku1 = reader["DM_TH_BK1_11"] is DBNull ? String.Empty : reader["DM_TH_BK1_11"].ToString();
                    string tahunBuku2 = reader["DM_TH_BK2_11"] is DBNull ? String.Empty : reader["DM_TH_BK2_11"].ToString();
                    f.MasaPajak = masa1 + " s/d " + masa2 + " " + tahun;
                    f.I_A = reader["DM_IA_DPP_11"] is DBNull ? String.Empty : reader["DM_IA_DPP_11"].ToString();
                    f.I_B = reader["DM_IB_DPP_11"] is DBNull ? String.Empty : reader["DM_IB_DPP_11"].ToString();
                    f.I_C = reader["DM_IC_DPP_11"] is DBNull ? String.Empty : reader["DM_IC_DPP_11"].ToString();
                    f.II_A_1 = reader["DM_IIA1_PPN_11"] is DBNull ? String.Empty : reader["DM_IIA1_PPN_11"].ToString();
                    f.II_A_2 = reader["DM_IIA2_PPN_11"] is DBNull ? String.Empty : reader["DM_IIA2_PPN_11"].ToString();
                    f.II_A_3 = reader["DM_IIA3_PPN_11"] is DBNull ? String.Empty : reader["DM_IIA3_PPN_11"].ToString();
                    f.II_B = reader["DM_IIB_PPN_11"] is DBNull ? String.Empty : reader["DM_IIB_PPN_11"].ToString();
                    f.II_C_1_PRCN = reader["DM_IIC1_PRCN_11"] is DBNull ? String.Empty : reader["DM_IIC1_PRCN_11"].ToString();
                    f.II_C_1 = reader["DM_IIC1_PPN_11"] is DBNull ? String.Empty : reader["DM_IIC1_PPN_11"].ToString();
                    f.II_C_2_PRCN = reader["DM_IIC2_PRCN_11"] is DBNull ? String.Empty : reader["DM_IIC2_PRCN_11"].ToString();
                    f.II_C_2 = reader["DM_IIC2_PPN_11"] is DBNull ? String.Empty : reader["DM_IIC2_PPN_11"].ToString();
                    f.II_C_3 = reader["DM_IIC3_PPN_11"] is DBNull ? String.Empty : reader["DM_IIC3_PPN_11"].ToString();
                    f.II_D_1 = reader["DM_IID1_PPN_11"] is DBNull ? String.Empty : reader["DM_IID1_PPN_11"].ToString();
                    f.II_D_2_TGL = reader["DM_IID2_TGL_11"] is DBNull ? String.Empty : reader["DM_IID2_TGL_11"].ToString();
                    f.II_D_2 = reader["DM_IID2_PPN_11"] is DBNull ? String.Empty : reader["DM_IID2_PPN_11"].ToString();
                    f.II_D_3 = reader["DM_IID3_PPN_11"] is DBNull ? String.Empty : reader["DM_IID3_PPN_11"].ToString();
                    f.II_E = reader["DM_IIE_PPN_11"] is DBNull ? String.Empty : reader["DM_IIE_PPN_11"].ToString();
                    f.II_F = reader["DM_IIF_PPN_11"] is DBNull ? String.Empty : reader["DM_IIF_PPN_11"].ToString();
                    f.II_G = reader["DM_IIG_PPN_11"] is DBNull ? String.Empty : reader["DM_IIG_PPN_11"].ToString();
                    f.III_A = reader["DM_IIIA_11"] is DBNull ? String.Empty : reader["DM_IIIA_11"].ToString();
                    f.III_B = reader["DM_IIIB_11"] is DBNull ? String.Empty : reader["DM_IIIB_11"].ToString();
                    f.IV_A = reader["DM_IVA_11"] is DBNull ? String.Empty : reader["DM_IVA_11"].ToString();
                    f.IV_B = reader["DM_IVB_11"] is DBNull ? String.Empty : reader["DM_IVB_11"].ToString();
                    f.IV_C = reader["DM_IVC_11"] is DBNull ? String.Empty : reader["DM_IVC_11"].ToString();
                    f.IV_D = reader["DM_IVD_11"] is DBNull ? String.Empty : reader["DM_IVD_11"].ToString();
                    f.IV_E = reader["DM_IVE_11"] is DBNull ? String.Empty : reader["DM_IVE_11"].ToString();
                    string DMA_JumlahDPP = reader["DMA_SUB_JML_DPP_11"] is DBNull ? String.Empty : reader["DMA_SUB_JML_DPP_11"].ToString();
                    string DMA_JumlahPPN = reader["DMA_SUB_JML_PPN_11"] is DBNull ? String.Empty : reader["DMA_SUB_JML_PPN_11"].ToString();
                    string DMA_JumlahPPnBM = reader["DMA_SUB_JML_PPNBM_11"] is DBNull ? String.Empty : reader["DMA_SUB_JML_PPNBM_11"].ToString();
                    string DMR_JumlahDPP = reader["DMR_SUB_JML_DPP_11"] is DBNull ? String.Empty : reader["DMR_SUB_JML_DPP_11"].ToString();
                    string DMR_JumlahPPN = reader["DMR_SUB_JML_PPN_11"] is DBNull ? String.Empty : reader["DMR_SUB_JML_PPN_11"].ToString();
                    string DMR_JumlahPPnBM = reader["DMR_SUB_JML_PPNBM_11"] is DBNull ? String.Empty : reader["DMR_SUB_JML_PPNBM_11"].ToString();

                    f.A_Page = new Form1111DM_Page();
                    f.A_Page.DocId = docId;
                    f.A_Page.TotalDpp = DMA_JumlahDPP;
                    f.A_Page.TotalPpn = DMA_JumlahPPN;
                    f.A_Page.TotalPPnBM = DMA_JumlahPPnBM;

                    f.R_Page = new Form1111DM_Page();
                    f.R_Page.DocId = docId;
                    f.R_Page.TotalDpp = DMR_JumlahDPP;
                    f.R_Page.TotalPpn = DMR_JumlahPPN;
                    f.R_Page.TotalPPnBM = DMR_JumlahPPnBM;

                    return f;

                }
                else
                    return null;

            }
        }

        internal static void UpdateForm1111DM(Form1111DM form1111DM, string log, string originalLog, string statusAkhir)
        {
            using (OracleConnection con = new OracleConnection(Decrypt(ConfigurationManager.ConnectionStrings["Datacap"].ConnectionString)))
            {
                con.Open();
                using (OracleTransaction tx = con.BeginTransaction())
                {
                    OracleCommand cmd = new OracleCommand();
                    cmd.Connection = con;
                    cmd.Transaction = tx;

                    UpdateQAJob(form1111DM, log, originalLog, statusAkhir, cmd);
                    UpdateDMINDEX(form1111DM, cmd);
                    Update_A_Page(form1111DM, cmd);
                    Update_R_Page(form1111DM, cmd);

                    tx.Commit();
                }
            }

        }

        private static void Update_R_Page(Form1111DM form1111DM, OracleCommand cmd)
        {
            foreach (Form1111DM_Item item in form1111DM.R_Page.Items)
            {
                string sql = @"UPDATE DMR_BKPJKP_DN_11 SET
                                      DPP_R_11=:DPP_R_11,
                                      PPN_R_11=:PPN_R_11,
                                      PPNBM_R_11=:PPNBM_R_11
                                    WHERE DOCID=:DOCID AND ID=:ID";
                cmd.CommandText = sql;
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue(":DPP_R_11", item.Dpp);
                cmd.Parameters.AddWithValue(":PPN_R_11", item.Ppn);
                cmd.Parameters.AddWithValue(":PPNBM_R_11", item.PPnBM);
                cmd.Parameters.AddWithValue(":DOCID", form1111DM.DocId);
                cmd.Parameters.AddWithValue(":ID", item.Id);
                cmd.ExecuteNonQuery();
            }
        }

        private static void Update_A_Page(Form1111DM form1111DM, OracleCommand cmd)
        {
            foreach (Form1111DM_Item item in form1111DM.A_Page.Items)
            {
                string sql = @"UPDATE DMA_PDN_11 SET
                                      DPP_A_11=:DPP_A_11,
                                      PPN_A_11=:PPN_A_11,
                                      PPNBM_A_11=:PPNBM_A_11
                                    WHERE DOCID=:DOCID AND ID=:ID";
                cmd.CommandText = sql;
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue(":DPP_A_11", item.Dpp);
                cmd.Parameters.AddWithValue(":PPN_A_11", item.Ppn);
                cmd.Parameters.AddWithValue(":PPNBM_A_11", item.PPnBM);
                cmd.Parameters.AddWithValue(":DOCID", form1111DM.DocId);
                cmd.Parameters.AddWithValue(":ID", item.Id);
                cmd.ExecuteNonQuery();

            }
        }

        internal static Form1111 GetForm1111(string docId)
        {
            using (OracleConnection con = new OracleConnection(Decrypt(ConfigurationManager.ConnectionStrings["Datacap"].ConnectionString)))
            {
                con.Open();

                Form1111 form = GetMainPage1(docId, con);
                if (form == null) return form;

                Fill_A1_Page(form, con);
                Fill_A2_Page(form, con);
                Fill_B1_Page(form, con);
                Fill_B2_Page(form, con);
                Fill_B3_Page(form, con);

                return form;
            }
        }

        private static Form1111 GetMainPage1(string docId, OracleConnection con)
        {
            string sql = @"SELECT
                                  DOCID,
                                  P_NAMAPKP_11,
                                  P_ALAMAT_11,
                                  P_NPWP_11,
                                  P_MASA1_11,
                                  P_MASA2_11,
                                  P_TAHUN_11,
                                  P_THBUKU1_11,
                                  P_THBUKU2_11,
                                  P_IA1_DPP_11,
                                  P_IA2_DPP_11,
                                  P_IA2_PPN_11,
                                  P_IA3_DPP_11,
                                  P_IA3_PPN_11,
                                  P_IA4_DPP_11,
                                  P_IA4_PPN_11,
                                  P_IA5_DPP_11,
                                  P_IA5_PPN_11,
                                  P_IA_JML_DPP_11,
                                  P_IA_JML_PPN_11,
                                  P_IB_11,
                                  P_IC_JML_11,
                                  P_IIA_11,
                                  P_IIB_11,
                                  P_IIC_11,
                                  P_IID_11,
                                  P_IIE_11,
                                  P_IIF_11,
                                  P_IIIA_11,
                                  P_IIIB_11,
                                  P_IVA_11,
                                  P_VA_11,
                                  P_VB_11,
                                  P_VC_11,
                                  P_VD_11,
                                  P_VE_11,
                                  A1_JML_DPP_11,
                                  A2_JML_DPP_11,
                                  A2_JML_PPN_11,
                                  A2_JML_PPNBM_11,
                                  B1_JML_DPP_11,
                                  B1_JML_PPN_11,
                                  B1_JML_PPNBM_11,
                                  B2_JML_DPP_11,
                                  B2_JML_PPN_11,
                                  B2_JML_PPNBM_11,
                                  B3_JML_DPP_11,
                                  B3_JML_PPN_11,
                                  B3_JML_PPNBM_11,
                                  AB_IA_DPP_11,
                                  AB_IB1_DPP_11,
                                  AB_IB1_PPN_11,
                                  AB_IB1_PPNBM_11,
                                  AB_IB2_DPP_11,
                                  AB_IB2_PPN_11,
                                  AB_IB2_PPNBM_11,
                                  AB_IC1_DPP_11,
                                  AB_IC1_PPN_11,
                                  AB_IC1_PPNBM_11,
                                  AB_IC2_DPP_11,
                                  AB_IC2_PPN_11,
                                  AB_IC2_PPNBM_11,
                                  AB_IC3_DPP_11,
                                  AB_IC3_PPN_11,
                                  AB_IC3_PPNBM_11,
                                  AB_IC4_DPP_11,
                                  AB_IC4_PPN_11,
                                  AB_IC4_PPNBM_11,
                                  AB_IIA_DPP_11,
                                  AB_IIA_PPN_11,
                                  AB_IIA_PPNBM_11,
                                  AB_IIB_DPP_11,
                                  AB_IIB_PPN_11,
                                  AB_IIB_PPNBM_11,
                                  AB_IIC_DPP_11,
                                  AB_IIC_PPN_11,
                                  AB_IIC_PPNBM_11,
                                  AB_IID_JML_DPP_11,
                                  AB_IID_JML_PPN_11,
                                  AB_IID_JML_PPNBM_11,
                                  AB_IIIA_PPN_11,
                                  AB_IIIB1_PPN_11,
                                  AB_IIIB2_PPN_11,
                                  AB_IIIB2_ISIAN_11,
                                  AB_IIIB3_PPN_11,
                                  AB_IIIB4_JML_PPN_11,
                                  AB_IIIC_JML_PPN_11
                                 FROM PPNINDEX_11
                                 WHERE DOCID=:DOCID";

            using (OracleCommand cmd = new OracleCommand(sql, con))
            {
                cmd.Parameters.AddWithValue(":DOCID", docId);
                OracleDataReader reader = cmd.ExecuteReader();

                if (reader.Read())
                {
                    Form1111 f = new Form1111();
                    f.DocId = reader["DOCID"] is DBNull ? String.Empty : reader["DOCID"].ToString();
                    f.NamaPkp = reader["P_NAMAPKP_11"] is DBNull ? String.Empty : reader["P_NAMAPKP_11"].ToString();
                    f.Alamat = reader["P_ALAMAT_11"] is DBNull ? String.Empty : reader["P_ALAMAT_11"].ToString();
                    f.Npwp = reader["P_NPWP_11"] is DBNull ? String.Empty : reader["P_NPWP_11"].ToString();
                    string masa1 = reader["P_MASA1_11"] is DBNull ? String.Empty : reader["P_MASA1_11"].ToString();
                    string masa2 = reader["P_MASA2_11"] is DBNull ? String.Empty : reader["P_MASA2_11"].ToString();
                    string tahun = reader["P_TAHUN_11"] is DBNull ? String.Empty : reader["P_TAHUN_11"].ToString();
                    string tahunBuku1 = reader["P_THBUKU1_11"] is DBNull ? String.Empty : reader["P_THBUKU1_11"].ToString();
                    string tahunBuku2 = reader["P_THBUKU2_11"] is DBNull ? String.Empty : reader["P_THBUKU2_11"].ToString();
                    f.MasaPajak = masa1 + " s/d " + masa2 + " " + tahun;
                    f.I_A_1 = reader["P_IA1_DPP_11"] is DBNull ? String.Empty : reader["P_IA1_DPP_11"].ToString();
                    f.I_A_2_DPP = reader["P_IA2_DPP_11"] is DBNull ? String.Empty : reader["P_IA2_DPP_11"].ToString();
                    f.I_A_2_PPN = reader["P_IA2_PPN_11"] is DBNull ? String.Empty : reader["P_IA2_PPN_11"].ToString();
                    f.I_A_3_DPP = reader["P_IA3_DPP_11"] is DBNull ? String.Empty : reader["P_IA3_DPP_11"].ToString();
                    f.I_A_3_PPN = reader["P_IA3_PPN_11"] is DBNull ? String.Empty : reader["P_IA3_PPN_11"].ToString();
                    f.I_A_4_DPP = reader["P_IA4_DPP_11"] is DBNull ? String.Empty : reader["P_IA4_DPP_11"].ToString();
                    f.I_A_4_PPN = reader["P_IA4_PPN_11"] is DBNull ? String.Empty : reader["P_IA4_PPN_11"].ToString();
                    f.I_A_5_DPP = reader["P_IA5_DPP_11"] is DBNull ? String.Empty : reader["P_IA5_DPP_11"].ToString();
                    f.I_A_5_PPN = reader["P_IA5_PPN_11"] is DBNull ? String.Empty : reader["P_IA5_PPN_11"].ToString();
                    f.I_A_Jml_DPP = reader["P_IA_JML_DPP_11"] is DBNull ? String.Empty : reader["P_IA_JML_DPP_11"].ToString();
                    f.I_A_Jml_PPN = reader["P_IA_JML_PPN_11"] is DBNull ? String.Empty : reader["P_IA_JML_PPN_11"].ToString();
                    f.I_B = reader["P_IB_11"] is DBNull ? String.Empty : reader["P_IB_11"].ToString();
                    f.I_C = reader["P_IC_JML_11"] is DBNull ? String.Empty : reader["P_IC_JML_11"].ToString();
                    f.II_A = reader["P_IIA_11"] is DBNull ? String.Empty : reader["P_IIA_11"].ToString();
                    f.II_B = reader["P_IIB_11"] is DBNull ? String.Empty : reader["P_IIB_11"].ToString();
                    f.II_C = reader["P_IIC_11"] is DBNull ? String.Empty : reader["P_IIC_11"].ToString();
                    f.II_D = reader["P_IID_11"] is DBNull ? String.Empty : reader["P_IID_11"].ToString();
                    f.II_E = reader["P_IIE_11"] is DBNull ? String.Empty : reader["P_IIE_11"].ToString();
                    f.II_F = reader["P_IIF_11"] is DBNull ? String.Empty : reader["P_IIF_11"].ToString();
                    f.III_A = reader["P_IIIA_11"] is DBNull ? String.Empty : reader["P_IIIA_11"].ToString();
                    f.III_B = reader["P_IIIB_11"] is DBNull ? String.Empty : reader["P_IIIB_11"].ToString();
                    f.IV_A = reader["P_IVA_11"] is DBNull ? String.Empty : reader["P_IVA_11"].ToString();
                    f.V_A = reader["P_VA_11"] is DBNull ? String.Empty : reader["P_VA_11"].ToString();
                    f.V_B = reader["P_VB_11"] is DBNull ? String.Empty : reader["P_VB_11"].ToString();
                    f.V_C = reader["P_VC_11"] is DBNull ? String.Empty : reader["P_VC_11"].ToString();
                    f.V_D = reader["P_VD_11"] is DBNull ? String.Empty : reader["P_VD_11"].ToString();
                    f.V_E = reader["P_VE_11"] is DBNull ? String.Empty : reader["P_VE_11"].ToString();
                    string A1_JumlahDPP = reader["A1_JML_DPP_11"] is DBNull ? String.Empty : reader["A1_JML_DPP_11"].ToString();
                    string A2_JumlahDPP = reader["A2_JML_DPP_11"] is DBNull ? String.Empty : reader["A2_JML_DPP_11"].ToString();
                    string A2_JumlahPPN = reader["A2_JML_PPN_11"] is DBNull ? String.Empty : reader["A2_JML_PPN_11"].ToString();
                    string A2_JumlahPPnBM = reader["A2_JML_PPNBM_11"] is DBNull ? String.Empty : reader["A2_JML_PPNBM_11"].ToString();
                    string B1_JumlahDPP = reader["B1_JML_DPP_11"] is DBNull ? String.Empty : reader["B1_JML_DPP_11"].ToString();
                    string B1_JumlahPPN = reader["B1_JML_PPN_11"] is DBNull ? String.Empty : reader["B1_JML_PPN_11"].ToString();
                    string B1_JumlahPPnBM = reader["B1_JML_PPNBM_11"] is DBNull ? String.Empty : reader["B1_JML_PPNBM_11"].ToString();
                    string B2_JumlahDPP = reader["B2_JML_DPP_11"] is DBNull ? String.Empty : reader["B2_JML_DPP_11"].ToString();
                    string B2_JumlahPPN = reader["B2_JML_PPN_11"] is DBNull ? String.Empty : reader["B2_JML_PPN_11"].ToString();
                    string B2_JumlahPPnBM = reader["B2_JML_PPNBM_11"] is DBNull ? String.Empty : reader["B2_JML_PPNBM_11"].ToString();
                    string B3_JumlahDPP = reader["B3_JML_DPP_11"] is DBNull ? String.Empty : reader["B3_JML_DPP_11"].ToString();
                    string B3_JumlahPPN = reader["B3_JML_PPN_11"] is DBNull ? String.Empty : reader["B3_JML_PPN_11"].ToString();
                    string B3_JumlahPPnBM = reader["B3_JML_PPNBM_11"] is DBNull ? String.Empty : reader["B3_JML_PPNBM_11"].ToString();

                    f.A1_Page = new Form1111_Page();
                    f.A1_Page.DocId = docId;
                    f.A1_Page.TotalDpp = A1_JumlahDPP;

                    f.A2_Page = new Form1111_Page();
                    f.A2_Page.DocId = docId;
                    f.A2_Page.TotalDpp = A2_JumlahDPP;
                    f.A2_Page.TotalPpn = A2_JumlahPPN;
                    f.A2_Page.TotalPPnBM = A2_JumlahPPnBM;

                    f.B1_Page = new Form1111_Page();
                    f.B1_Page.DocId = docId;
                    f.B1_Page.TotalDpp = B1_JumlahDPP;
                    f.B1_Page.TotalPpn = B1_JumlahPPN;
                    f.B1_Page.TotalPPnBM = B1_JumlahPPnBM;

                    f.B2_Page = new Form1111_Page();
                    f.B2_Page.DocId = docId;
                    f.B2_Page.TotalDpp = B2_JumlahDPP;
                    f.B2_Page.TotalPpn = B2_JumlahPPN;
                    f.B2_Page.TotalPPnBM = B2_JumlahPPnBM;

                    f.B3_Page = new Form1111_Page();
                    f.B3_Page.DocId = docId;
                    f.B3_Page.TotalDpp = B3_JumlahDPP;
                    f.B3_Page.TotalPpn = B3_JumlahPPN;
                    f.B3_Page.TotalPPnBM = B3_JumlahPPnBM;

                    f.I_A2 = reader["AB_IA_DPP_11"] is DBNull ? String.Empty : reader["AB_IA_DPP_11"].ToString();
                    f.I_B2_1_DPP = reader["AB_IB1_DPP_11"] is DBNull ? String.Empty : reader["AB_IB1_DPP_11"].ToString();
                    f.I_B2_1_PPN = reader["AB_IB1_PPN_11"] is DBNull ? String.Empty : reader["AB_IB1_PPN_11"].ToString();
                    f.I_B2_1_PPnBM = reader["AB_IB1_PPNBM_11"] is DBNull ? String.Empty : reader["AB_IB1_PPNBM_11"].ToString();
                    f.I_B2_2_DPP = reader["AB_IB2_DPP_11"] is DBNull ? String.Empty : reader["AB_IB2_DPP_11"].ToString();
                    f.I_B2_2_PPN = reader["AB_IB2_PPN_11"] is DBNull ? String.Empty : reader["AB_IB2_PPN_11"].ToString();
                    f.I_B2_2_PPnBM = reader["AB_IB2_PPNBM_11"] is DBNull ? String.Empty : reader["AB_IB2_PPNBM_11"].ToString();
                    f.I_C2_1_DPP = reader["AB_IC1_DPP_11"] is DBNull ? String.Empty : reader["AB_IC1_DPP_11"].ToString();
                    f.I_C2_1_PPN = reader["AB_IC1_PPN_11"] is DBNull ? String.Empty : reader["AB_IC1_PPN_11"].ToString();
                    f.I_C2_1_PPnBM = reader["AB_IC1_PPNBM_11"] is DBNull ? String.Empty : reader["AB_IC1_PPNBM_11"].ToString();
                    f.I_C2_2_DPP = reader["AB_IC2_DPP_11"] is DBNull ? String.Empty : reader["AB_IC2_DPP_11"].ToString();
                    f.I_C2_2_PPN = reader["AB_IC2_PPN_11"] is DBNull ? String.Empty : reader["AB_IC2_PPN_11"].ToString();
                    f.I_C2_2_PPnBM = reader["AB_IC2_PPNBM_11"] is DBNull ? String.Empty : reader["AB_IC2_PPNBM_11"].ToString();
                    f.I_C2_3_DPP = reader["AB_IC3_DPP_11"] is DBNull ? String.Empty : reader["AB_IC3_DPP_11"].ToString();
                    f.I_C2_3_PPN = reader["AB_IC3_PPN_11"] is DBNull ? String.Empty : reader["AB_IC3_PPN_11"].ToString();
                    f.I_C2_3_PPnBM = reader["AB_IC3_PPNBM_11"] is DBNull ? String.Empty : reader["AB_IC3_PPNBM_11"].ToString();
                    f.I_C2_4_DPP = reader["AB_IC4_DPP_11"] is DBNull ? String.Empty : reader["AB_IC4_DPP_11"].ToString();
                    f.I_C2_4_PPN = reader["AB_IC4_PPN_11"] is DBNull ? String.Empty : reader["AB_IC4_PPN_11"].ToString();
                    f.I_C2_4_PPnBM = reader["AB_IC4_PPNBM_11"] is DBNull ? String.Empty : reader["AB_IC4_PPNBM_11"].ToString();
                    f.II_A2_DPP = reader["AB_IIA_DPP_11"] is DBNull ? String.Empty : reader["AB_IIA_DPP_11"].ToString();
                    f.II_A2_PPN = reader["AB_IIA_PPN_11"] is DBNull ? String.Empty : reader["AB_IIA_PPN_11"].ToString();
                    f.II_A2_PPnBM = reader["AB_IIA_PPNBM_11"] is DBNull ? String.Empty : reader["AB_IIA_PPNBM_11"].ToString();
                    f.II_B2_DPP = reader["AB_IIB_DPP_11"] is DBNull ? String.Empty : reader["AB_IIB_DPP_11"].ToString();
                    f.II_B2_PPN = reader["AB_IIB_PPN_11"] is DBNull ? String.Empty : reader["AB_IIB_PPN_11"].ToString();
                    f.II_B2_PPnBM = reader["AB_IIB_PPNBM_11"] is DBNull ? String.Empty : reader["AB_IIB_PPNBM_11"].ToString();
                    f.II_C2_DPP = reader["AB_IIC_DPP_11"] is DBNull ? String.Empty : reader["AB_IIC_DPP_11"].ToString();
                    f.II_C2_PPN = reader["AB_IIC_PPN_11"] is DBNull ? String.Empty : reader["AB_IIC_PPN_11"].ToString();
                    f.II_C2_PPnBM = reader["AB_IIC_PPNBM_11"] is DBNull ? String.Empty : reader["AB_IIC_PPNBM_11"].ToString();
                    f.II_D2_DPP = reader["AB_IID_JML_DPP_11"] is DBNull ? String.Empty : reader["AB_IID_JML_DPP_11"].ToString();
                    f.II_D2_PPN = reader["AB_IID_JML_PPN_11"] is DBNull ? String.Empty : reader["AB_IID_JML_PPN_11"].ToString();
                    f.II_D2_PPnBM = reader["AB_IID_JML_PPNBM_11"] is DBNull ? String.Empty : reader["AB_IID_JML_PPNBM_11"].ToString();
                    f.III_A2 = reader["AB_IIIA_PPN_11"] is DBNull ? String.Empty : reader["AB_IIIA_PPN_11"].ToString();
                    f.III_B2_1 = reader["AB_IIIB1_PPN_11"] is DBNull ? String.Empty : reader["AB_IIIB1_PPN_11"].ToString();
                    f.III_B2_2_ISIAN = reader["AB_IIIB2_ISIAN_11"] is DBNull ? String.Empty : reader["AB_IIIB2_ISIAN_11"].ToString();
                    f.III_B2_2 = reader["AB_IIIB2_PPN_11"] is DBNull ? String.Empty : reader["AB_IIIB2_PPN_11"].ToString();
                    f.III_B2_3 = reader["AB_IIIB3_PPN_11"] is DBNull ? String.Empty : reader["AB_IIIB3_PPN_11"].ToString();
                    f.III_B2_4 = reader["AB_IIIB4_JML_PPN_11"] is DBNull ? String.Empty : reader["AB_IIIB4_JML_PPN_11"].ToString();
                    f.III_C2 = reader["AB_IIIC_JML_PPN_11"] is DBNull ? String.Empty : reader["AB_IIIC_JML_PPN_11"].ToString();

                    return f;

                }
                else
                    return null;

            }
        }

        internal static DataSet GetSPT1111(string docId)
        {
            using (DataSet dsRet = new DataSet())
            {
                string sql = @"SELECT
                                  DOCID,
                                  P_NAMAPKP_11,
                                  P_ALAMAT_11,
                                  P_NPWP_11,
                                  P_MASA1_11,
                                  P_MASA2_11,
                                  P_TAHUN_11,
                                  P_THBUKU1_11,
                                  P_THBUKU2_11,
                                  P_IA1_DPP_11,
                                  P_IA2_DPP_11,
                                  P_IA2_PPN_11,
                                  P_IA3_DPP_11,
                                  P_IA3_PPN_11,
                                  P_IA4_DPP_11,
                                  P_IA4_PPN_11,
                                  P_IA5_DPP_11,
                                  P_IA5_PPN_11,
                                  P_IA_JML_DPP_11,
                                  P_IA_JML_PPN_11,
                                  P_IB_11,
                                  P_IC_JML_11,
                                  P_IIA_11,
                                  P_IIB_11,
                                  P_IIC_11,
                                  P_IID_11,
                                  P_IIE_11,
                                  P_IIF_11,
                                  P_IIIA_11,
                                  P_IIIB_11,
                                  P_IVA_11,
                                  P_VA_11,
                                  P_VB_11,
                                  P_VC_11,
                                  P_VD_11,
                                  P_VE_11,
                                  A1_JML_DPP_11,
                                  A2_JML_DPP_11,
                                  A2_JML_PPN_11,
                                  A2_JML_PPNBM_11,
                                  B1_JML_DPP_11,
                                  B1_JML_PPN_11,
                                  B1_JML_PPNBM_11,
                                  B2_JML_DPP_11,
                                  B2_JML_PPN_11,
                                  B2_JML_PPNBM_11,
                                  B3_JML_DPP_11,
                                  B3_JML_PPN_11,
                                  B3_JML_PPNBM_11,
                                  AB_IA_DPP_11,
                                  AB_IB1_DPP_11,
                                  AB_IB1_PPN_11,
                                  AB_IB1_PPNBM_11,
                                  AB_IB2_DPP_11,
                                  AB_IB2_PPN_11,
                                  AB_IB2_PPNBM_11,
                                  AB_IC1_DPP_11,
                                  AB_IC1_PPN_11,
                                  AB_IC1_PPNBM_11,
                                  AB_IC2_DPP_11,
                                  AB_IC2_PPN_11,
                                  AB_IC2_PPNBM_11,
                                  AB_IC3_DPP_11,
                                  AB_IC3_PPN_11,
                                  AB_IC3_PPNBM_11,
                                  AB_IC4_DPP_11,
                                  AB_IC4_PPN_11,
                                  AB_IC4_PPNBM_11,
                                  AB_IIA_DPP_11,
                                  AB_IIA_PPN_11,
                                  AB_IIA_PPNBM_11,
                                  AB_IIB_DPP_11,
                                  AB_IIB_PPN_11,
                                  AB_IIB_PPNBM_11,
                                  AB_IIC_DPP_11,
                                  AB_IIC_PPN_11,
                                  AB_IIC_PPNBM_11,
                                  AB_IID_JML_DPP_11,
                                  AB_IID_JML_PPN_11,
                                  AB_IID_JML_PPNBM_11,
                                  AB_IIIA_PPN_11,
                                  AB_IIIB1_PPN_11,
                                  AB_IIIB2_PPN_11,
                                  AB_IIIB2_ISIAN_11,
                                  AB_IIIB3_PPN_11,
                                  AB_IIIB4_JML_PPN_11,
                                  AB_IIIC_JML_PPN_11
                                 FROM PPNINDEX_11
                                 WHERE DOCID=:DOCID";

                using (OracleConnection con = new OracleConnection(Decrypt(ConfigurationManager.ConnectionStrings["Datacap"].ConnectionString)))
                {
                    con.Open();
                    using (OracleCommand cmd = new OracleCommand(sql, con))
                    {
                        cmd.Parameters.AddWithValue(":DOCID", docId);
                        OracleDataAdapter adapter = new OracleDataAdapter(cmd);
                        adapter.Fill(dsRet, "PPNINDEX_11");
                    }
                }
                return dsRet;
            }
        }

        /*internal static DataSet GetSPT(string docId, string tableSource)
        {
            using (DataSet dsRet = new DataSet())
            {
                string sql = @"SELECT
                                  *
                                 FROM "+tableSource+
                                 " WHERE DOCID=:DOCID";

                using (OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["Datacap"].ConnectionString))
                {
                    con.Open();
                    using (OracleCommand cmd = new OracleCommand(sql, con))
                    {
                        cmd.Parameters.AddWithValue(":DOCID", docId);
                        OracleDataAdapter adapter = new OracleDataAdapter(cmd);
                        adapter.Fill(dsRet, tableSource);
                    }
                }
                return dsRet;
            }
        }*/

        internal static DataTable GetSPT(string docId, string tableSource)
        {
            using (DataSet dsRet = new DataSet())
            {
                string sql = @"SELECT
                                  *
                                 FROM " + tableSource +
                                 " WHERE DOCID=:DOCID";

                using (OracleConnection con = new OracleConnection(Decrypt(ConfigurationManager.ConnectionStrings["Datacap"].ConnectionString)))
                {
                    con.Open();
                    using (OracleCommand cmd = new OracleCommand(sql, con))
                    {
                        cmd.Parameters.AddWithValue(":DOCID", docId);
                        OracleDataAdapter adapter = new OracleDataAdapter(cmd);
                        adapter.Fill(dsRet, tableSource);
                    }
                }
                return dsRet.Tables[tableSource];
            }
        }

        internal static DataTable GetTableWithFieldByField(OracleConnection con, string tableSource, string[] fieldName, string docId, string barcode)
        {
            string sFieldName = string.Empty;

            sFieldName = StringUtil.ConvertToString(fieldName);

            using (DataSet dsRet = new DataSet())
            {
                string sql = @"SELECT "
                                  + sFieldName +
                                 " FROM " + tableSource +
                                 " WHERE DOCID=:DOCID AND BARCODE=:BARCODE";

                con.Open();
                using (OracleCommand cmd = new OracleCommand(sql, con))
                {
                    cmd.Parameters.AddWithValue(":DOCID", docId);
                    cmd.Parameters.AddWithValue(":BARCODE", barcode);
                    OracleDataAdapter adapter = new OracleDataAdapter(cmd);
                    adapter.Fill(dsRet, tableSource);
                }

                return dsRet.Tables[tableSource];
            }
        }

        internal static DataTable GetTableWithFieldByField(OracleConnection con, string tableSource, string[] fieldName, string docId)
        {
            string sFieldName = string.Empty;

            sFieldName = StringUtil.ConvertToString(fieldName);

            using (DataSet dsRet = new DataSet())
            {
                string sql = @"SELECT "
                                  + sFieldName +
                                 " FROM " + tableSource +
                                 " WHERE DOCID=:DOCID";

                con.Open();
                using (OracleCommand cmd = new OracleCommand(sql, con))
                {
                    cmd.Parameters.AddWithValue(":DOCID", docId);
                    OracleDataAdapter adapter = new OracleDataAdapter(cmd);
                    adapter.Fill(dsRet, tableSource);
                }

                return dsRet.Tables[tableSource];
            }
        }

        internal static DataTable GetTable1108(OracleConnection con, string tableSource, string[] fieldName, string docId, string tableNo)
        {
            string sFieldName = string.Empty;

            sFieldName = StringUtil.ConvertToString(fieldName);

            using (DataSet dsRet = new DataSet())
            {
                string sql = @"SELECT "
                                  + sFieldName +
                                 " FROM " + tableSource +
                                 " WHERE DOCID=:DOCID AND TABLENO=:TABLENO";

                con.Open();
                using (OracleCommand cmd = new OracleCommand(sql, con))
                {
                    cmd.Parameters.AddWithValue(":DOCID", docId);
                    cmd.Parameters.AddWithValue(":TABLENO", tableNo);
                    OracleDataAdapter adapter = new OracleDataAdapter(cmd);
                    adapter.Fill(dsRet, tableSource);
                }

                return dsRet.Tables[tableSource];
            }
        }

        private static void Fill_A1_Page(Form1111 form, OracleConnection con)
        {
            string sql = @"SELECT
                              DOCID,
                              A1_NAPEM_11,
                              A1_NODOK_11,
                              A1_TGL_11,
                              A1_DPP_11,
                              A1_KET_11,
                              ID
                            FROM PPNA1EXP_11
                            WHERE DOCID=:DOCID";

            using (OracleCommand cmd = new OracleCommand(sql, con))
            {
                cmd.Parameters.AddWithValue(":DOCID", form.DocId);
                OracleDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    Form1111_Item i = new Form1111_Item();
                    i.DocId = reader["DOCID"] is DBNull ? String.Empty : reader["DOCID"].ToString();
                    i.Nama = reader["A1_NAPEM_11"] is DBNull ? String.Empty : reader["A1_NAPEM_11"].ToString();
                    i.Nomor = reader["A1_NODOK_11"] is DBNull ? String.Empty : reader["A1_NODOK_11"].ToString();
                    i.Tanggal = reader["A1_TGL_11"] is DBNull ? String.Empty : reader["A1_TGL_11"].ToString();
                    i.Dpp = reader["A1_DPP_11"] is DBNull ? String.Empty : reader["A1_DPP_11"].ToString();
                    i.Ket = reader["A1_KET_11"] is DBNull ? String.Empty : reader["A1_KET_11"].ToString();
                    i.Id = reader["ID"] is DBNull ? String.Empty : reader["ID"].ToString();

                    form.A1_Page.Items.Add(i);
                }
            }
        }

        private static void Fill_A2_Page(Form1111 form, OracleConnection con)
        {
            string sql = @"SELECT
                              DOCID,
                              A2_NAPEM_11,
                              A2_NOPWP_11,
                              A2_KODE_11,
                              A2_TGL_11,
                              A2_DPP_11,
                              A2_PPN_11,
                              A2_PPNBM_11,
                              ID
                            FROM PPNA2PJKKLR_11
                            WHERE DOCID=:DOCID";

            using (OracleCommand cmd = new OracleCommand(sql, con))
            {
                cmd.Parameters.AddWithValue(":DOCID", form.DocId);
                OracleDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    Form1111_Item i = new Form1111_Item();
                    i.DocId = reader["DOCID"] is DBNull ? String.Empty : reader["DOCID"].ToString();
                    i.Nama = reader["A2_NAPEM_11"] is DBNull ? String.Empty : reader["A2_NAPEM_11"].ToString();
                    i.Npwp = reader["A2_NOPWP_11"] is DBNull ? String.Empty : reader["A2_NOPWP_11"].ToString();
                    i.Kode_no_seri = reader["A2_KODE_11"] is DBNull ? String.Empty : reader["A2_KODE_11"].ToString();
                    i.Tanggal = reader["A2_TGL_11"] is DBNull ? String.Empty : reader["A2_TGL_11"].ToString();
                    i.Dpp = reader["A2_DPP_11"] is DBNull ? String.Empty : reader["A2_DPP_11"].ToString();
                    i.Ppn = reader["A2_PPN_11"] is DBNull ? String.Empty : reader["A2_PPN_11"].ToString();
                    i.PPnBM = reader["A2_PPNBM_11"] is DBNull ? String.Empty : reader["A2_PPNBM_11"].ToString();
                    i.Id = reader["ID"] is DBNull ? String.Empty : reader["ID"].ToString();

                    form.A2_Page.Items.Add(i);
                }
            }
        }

        private static void Fill_B1_Page(Form1111 form, OracleConnection con)
        {
            string sql = @"SELECT
                              DOCID,
                              B1_NAPEN_11,
                              B1_NODOK_11,
                              B1_TGL_11,
                              B1_DPP_11,
                              B1_PPN_11,
                              B1_PPNBM_11,
                              B1_KET_11,
                              ID
                            FROM PPNB1PJKMSK_11
                            WHERE DOCID=:DOCID";

            using (OracleCommand cmd = new OracleCommand(sql, con))
            {
                cmd.Parameters.AddWithValue(":DOCID", form.DocId);
                OracleDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    Form1111_Item i = new Form1111_Item();
                    i.DocId = reader["DOCID"] is DBNull ? String.Empty : reader["DOCID"].ToString();
                    i.Nama = reader["B1_NAPEN_11"] is DBNull ? String.Empty : reader["B1_NAPEN_11"].ToString();
                    i.Nomor = reader["B1_NODOK_11"] is DBNull ? String.Empty : reader["B1_NODOK_11"].ToString();
                    i.Tanggal = reader["B1_TGL_11"] is DBNull ? String.Empty : reader["B1_TGL_11"].ToString();
                    i.Dpp = reader["B1_DPP_11"] is DBNull ? String.Empty : reader["B1_DPP_11"].ToString();
                    i.Ppn = reader["B1_PPN_11"] is DBNull ? String.Empty : reader["B1_PPN_11"].ToString();
                    i.PPnBM = reader["B1_PPNBM_11"] is DBNull ? String.Empty : reader["B1_PPNBM_11"].ToString();
                    i.Ket = reader["B1_KET_11"] is DBNull ? String.Empty : reader["B1_KET_11"].ToString();
                    i.Id = reader["ID"] is DBNull ? String.Empty : reader["ID"].ToString();

                    form.B1_Page.Items.Add(i);
                }
            }
        }

        private static void Fill_B2_Page(Form1111 form, OracleConnection con)
        {
            string sql = @"SELECT
                              DOCID,
                              B2_NAPEN_11,
                              B2_NOPWP_11,
                              B2_KODE_11,
                              B2_TGL_11,
                              B2_DPP_11,
                              B2_PPN_11,
                              B2_PPNBM_11,
                              ID
                            FROM PPNB2PJKMSK_11
                            WHERE DOCID=:DOCID";

            using (OracleCommand cmd = new OracleCommand(sql, con))
            {
                cmd.Parameters.AddWithValue(":DOCID", form.DocId);
                OracleDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    Form1111_Item i = new Form1111_Item();
                    i.DocId = reader["DOCID"] is DBNull ? String.Empty : reader["DOCID"].ToString();
                    i.Nama = reader["B2_NAPEN_11"] is DBNull ? String.Empty : reader["B2_NAPEN_11"].ToString();
                    i.Npwp = reader["B2_NOPWP_11"] is DBNull ? String.Empty : reader["B2_NOPWP_11"].ToString();
                    i.Kode_no_seri = reader["B2_KODE_11"] is DBNull ? String.Empty : reader["B2_KODE_11"].ToString();
                    i.Tanggal = reader["B2_TGL_11"] is DBNull ? String.Empty : reader["B2_TGL_11"].ToString();
                    i.Dpp = reader["B2_DPP_11"] is DBNull ? String.Empty : reader["B2_DPP_11"].ToString();
                    i.Ppn = reader["B2_PPN_11"] is DBNull ? String.Empty : reader["B2_PPN_11"].ToString();
                    i.PPnBM = reader["B2_PPNBM_11"] is DBNull ? String.Empty : reader["B2_PPNBM_11"].ToString();
                    i.Id = reader["ID"] is DBNull ? String.Empty : reader["ID"].ToString();

                    form.B2_Page.Items.Add(i);
                }
            }
        }

        private static void Fill_B3_Page(Form1111 form, OracleConnection con)
        {
            string sql = @"SELECT
                              DOCID,
                              B3_NAPEN_11,
                              B3_NOPWP_11,
                              B3_KODE_11,
                              B3_TGL_11,
                              B3_DPP_11,
                              B3_PPN_11,
                              B3_PPNBM_11,
                              ID
                            FROM PPNB3PJKMSK_11
                            WHERE DOCID=:DOCID";

            using (OracleCommand cmd = new OracleCommand(sql, con))
            {
                cmd.Parameters.AddWithValue(":DOCID", form.DocId);
                OracleDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    Form1111_Item i = new Form1111_Item();
                    i.DocId = reader["DOCID"] is DBNull ? String.Empty : reader["DOCID"].ToString();
                    i.Nama = reader["B3_NAPEN_11"] is DBNull ? String.Empty : reader["B3_NAPEN_11"].ToString();
                    i.Npwp = reader["B3_NOPWP_11"] is DBNull ? String.Empty : reader["B3_NOPWP_11"].ToString();
                    i.Kode_no_seri = reader["B3_KODE_11"] is DBNull ? String.Empty : reader["B3_KODE_11"].ToString();
                    i.Tanggal = reader["B3_TGL_11"] is DBNull ? String.Empty : reader["B3_TGL_11"].ToString();
                    i.Dpp = reader["B3_DPP_11"] is DBNull ? String.Empty : reader["B3_DPP_11"].ToString();
                    i.Ppn = reader["B3_PPN_11"] is DBNull ? String.Empty : reader["B3_PPN_11"].ToString();
                    i.PPnBM = reader["B3_PPNBM_11"] is DBNull ? String.Empty : reader["B3_PPNBM_11"].ToString();
                    i.Id = reader["ID"] is DBNull ? String.Empty : reader["ID"].ToString();

                    form.B3_Page.Items.Add(i);
                }
            }
        }

        internal static void UpdateForm1111(Form1111 form1111, string log, string originalLog, string statusAkhir)
        {
            using (OracleConnection con = new OracleConnection(Decrypt(ConfigurationManager.ConnectionStrings["Datacap"].ConnectionString)))
            {
                con.Open();
                using (OracleTransaction tx = con.BeginTransaction())
                {
                    OracleCommand cmd = new OracleCommand();
                    cmd.Connection = con;
                    cmd.Transaction = tx;

                    UpdateQAJob1(form1111, log, originalLog, statusAkhir, cmd);
                    UpdateDMINDEX1(form1111, cmd);
                    Update_A1_Page(form1111, cmd);
                    Update_A2_Page(form1111, cmd);
                    Update_B1_Page(form1111, cmd);
                    Update_B2_Page(form1111, cmd);
                    Update_B3_Page(form1111, cmd);

                    tx.Commit();
                }
            }

        }

        private static void Update_A1_Page(Form1111 form1111, OracleCommand cmd)
        {
            foreach (Form1111_Item item in form1111.A1_Page.Items)
            {
                string sql = @"UPDATE PPNA1EXP_11 SET
                                      A1_DPP_11=:A1_DPP_11
                                    WHERE DOCID=:DOCID AND ID=:ID";
                cmd.CommandText = sql;
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue(":A1_DPP_11", item.Dpp);
                cmd.Parameters.AddWithValue(":DOCID", form1111.DocId);
                cmd.Parameters.AddWithValue(":ID", item.Id);
                cmd.ExecuteNonQuery();

            }
        }

        private static void Update_A2_Page(Form1111 form1111, OracleCommand cmd)
        {
            foreach (Form1111_Item item in form1111.A2_Page.Items)
            {
                string sql = @"UPDATE PPNA2PJKKLR_11 SET
                                      A2_DPP_11=:A2_DPP_11,
                                      A2_PPN_11=:A2_PPN_11,
                                      A2_PPNBM_11=:A2_PPNBM_11
                                    WHERE DOCID=:DOCID AND ID=:ID";
                cmd.CommandText = sql;
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue(":A2_DPP_11", item.Dpp);
                cmd.Parameters.AddWithValue(":A2_PPN_11", item.Ppn);
                cmd.Parameters.AddWithValue(":A2_PPNBM_11", item.PPnBM);
                cmd.Parameters.AddWithValue(":DOCID", form1111.DocId);
                cmd.Parameters.AddWithValue(":ID", item.Id);
                cmd.ExecuteNonQuery();
            }
        }

        private static void Update_B1_Page(Form1111 form1111, OracleCommand cmd)
        {
            foreach (Form1111_Item item in form1111.B1_Page.Items)
            {
                string sql = @"UPDATE PPNB1PJKMSK_11 SET
                                      B1_DPP_11=:B1_DPP_11,
                                      B1_PPN_11=:B1_PPN_11,
                                      B1_PPNBM_11=:B1_PPNBM_11
                                    WHERE DOCID=:DOCID AND ID=:ID";
                cmd.CommandText = sql;
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue(":B1_DPP_11", item.Dpp);
                cmd.Parameters.AddWithValue(":B1_PPN_11", item.Ppn);
                cmd.Parameters.AddWithValue(":B1_PPNBM_11", item.PPnBM);
                cmd.Parameters.AddWithValue(":DOCID", form1111.DocId);
                cmd.Parameters.AddWithValue(":ID", item.Id);
                cmd.ExecuteNonQuery();
            }
        }

        private static void Update_B2_Page(Form1111 form1111, OracleCommand cmd)
        {
            foreach (Form1111_Item item in form1111.B2_Page.Items)
            {
                string sql = @"UPDATE PPNB2PJKMSK_11 SET
                                      B2_DPP_11=:B2_DPP_11,
                                      B2_PPN_11=:B2_PPN_11,
                                      B2_PPNBM_11=:B2_PPNBM_11
                                    WHERE DOCID=:DOCID AND ID=:ID";
                cmd.CommandText = sql;
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue(":B2_DPP_11", item.Dpp);
                cmd.Parameters.AddWithValue(":B2_PPN_11", item.Ppn);
                cmd.Parameters.AddWithValue(":B2_PPNBM_11", item.PPnBM);
                cmd.Parameters.AddWithValue(":DOCID", form1111.DocId);
                cmd.Parameters.AddWithValue(":ID", item.Id);
                cmd.ExecuteNonQuery();
            }
        }

        private static void Update_B3_Page(Form1111 form1111, OracleCommand cmd)
        {
            foreach (Form1111_Item item in form1111.B3_Page.Items)
            {
                string sql = @"UPDATE PPNB3PJKMSK_11 SET
                                      B3_DPP_11=:B3_DPP_11,
                                      B3_PPN_11=:B3_PPN_11,
                                      B3_PPNBM_11=:B3_PPNBM_11
                                    WHERE DOCID=:DOCID AND ID=:ID";
                cmd.CommandText = sql;
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue(":B3_DPP_11", item.Dpp);
                cmd.Parameters.AddWithValue(":B3_PPN_11", item.Ppn);
                cmd.Parameters.AddWithValue(":B3_PPNBM_11", item.PPnBM);
                cmd.Parameters.AddWithValue(":DOCID", form1111.DocId);
                cmd.Parameters.AddWithValue(":ID", item.Id);
                cmd.ExecuteNonQuery();
            }
        }

        private static void UpdateDMINDEX(Form1111DM form1111DM, OracleCommand cmd)
        {
            string sql = @"UPDATE DMINDEX_11 SET 
                                        DM_IA_DPP_11=:DM_IA_DPP_11, 
                                        DM_IB_DPP_11=:DM_IB_DPP_11, 
                                        DM_IC_DPP_11=:DM_IC_DPP_11, 
                                        DM_IIA1_PPN_11=:DM_IIA1_PPN_11, 
                                        DM_IIA2_PPN_11=:DM_IIA2_PPN_11, 
                                        DM_IIA3_PPN_11=:DM_IIA3_PPN_11, 
                                        DM_IIB_PPN_11=:DM_IIB_PPN_11, 
                                        DM_IIC1_PPN_11=:DM_IIC1_PPN_11, 
                                        DM_IIC2_PPN_11=:DM_IIC2_PPN_11, 
                                        DM_IIC3_PPN_11=:DM_IIC3_PPN_11, 
                                        DM_IID1_PPN_11=:DM_IID1_PPN_11, 
                                        DM_IID2_PPN_11=:DM_IID2_PPN_11, 
                                        DM_IID3_PPN_11=:DM_IID3_PPN_11, 
                                        DM_IIE_PPN_11=:DM_IIE_PPN_11, 
                                        DM_IIF_PPN_11=:DM_IIF_PPN_11, 
                                        DM_IIG_PPN_11=:DM_IIG_PPN_11, 
                                        DM_IIIA_11=:DM_IIIA_11, 
                                        DM_IIIB_11=:DM_IIIB_11, 
                                        DM_IVA_11=:DM_IVA_11, 
                                        DM_IVB_11=:DM_IVB_11, 
                                        DM_IVC_11=:DM_IVC_11, 
                                        DM_IVD_11=:DM_IVD_11, 
                                        DM_IVE_11=:DM_IVE_11, 
                                        DMA_SUB_JML_DPP_11=:DMA_SUB_JML_DPP_11, 
                                        DMA_SUB_JML_PPN_11=:DMA_SUB_JML_PPN_11, 
                                        DMA_SUB_JML_PPNBM_11=:DMA_SUB_JML_PPNBM_11, 
                                        DMR_SUB_JML_DPP_11=:DMR_SUB_JML_DPP_11, 
                                        DMR_SUB_JML_PPN_11=:DMR_SUB_JML_PPN_11, 
                                        DMR_SUB_JML_PPNBM_11=:DMR_SUB_JML_PPNBM_11 
                                    WHERE DOCID=:DOCID";
            cmd.CommandText = sql;
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue(":DM_IA_DPP_11", form1111DM.I_A);
            cmd.Parameters.AddWithValue(":DM_IB_DPP_11", form1111DM.I_B);
            cmd.Parameters.AddWithValue(":DM_IC_DPP_11", form1111DM.I_C);
            cmd.Parameters.AddWithValue(":DM_IIA1_PPN_11", form1111DM.II_A_1);
            cmd.Parameters.AddWithValue(":DM_IIA2_PPN_11", form1111DM.II_A_2);
            cmd.Parameters.AddWithValue(":DM_IIA3_PPN_11", form1111DM.II_A_3);
            cmd.Parameters.AddWithValue(":DM_IIB_PPN_11", form1111DM.II_B);
            cmd.Parameters.AddWithValue(":DM_IIC1_PPN_11", form1111DM.II_C_1);
            cmd.Parameters.AddWithValue(":DM_IIC2_PPN_11", form1111DM.II_C_2);
            cmd.Parameters.AddWithValue(":DM_IIC3_PPN_11", form1111DM.II_C_3);
            cmd.Parameters.AddWithValue(":DM_IID1_PPN_11", form1111DM.II_D_1);
            cmd.Parameters.AddWithValue(":DM_IID2_PPN_11", form1111DM.II_D_2);
            cmd.Parameters.AddWithValue(":DM_IID3_PPN_11", form1111DM.II_D_3);
            cmd.Parameters.AddWithValue(":DM_IIE_PPN_11", form1111DM.II_E);
            cmd.Parameters.AddWithValue(":DM_IIF_PPN_11", form1111DM.II_F);
            cmd.Parameters.AddWithValue(":DM_IIG_PPN_11", form1111DM.II_G);
            cmd.Parameters.AddWithValue(":DM_IIIA_11", form1111DM.III_A);
            cmd.Parameters.AddWithValue(":DM_IIIB_11", form1111DM.III_B);
            cmd.Parameters.AddWithValue(":DM_IVA_11", form1111DM.IV_A);
            cmd.Parameters.AddWithValue(":DM_IVB_11", form1111DM.IV_B);
            cmd.Parameters.AddWithValue(":DM_IVC_11", form1111DM.IV_C);
            cmd.Parameters.AddWithValue(":DM_IVD_11", form1111DM.IV_D);
            cmd.Parameters.AddWithValue(":DM_IVE_11", form1111DM.IV_E);
            cmd.Parameters.AddWithValue(":DMA_SUB_JML_DPP_11", form1111DM.A_Page.TotalDpp);
            cmd.Parameters.AddWithValue(":DMA_SUB_JML_PPN_11", form1111DM.A_Page.TotalPpn);
            cmd.Parameters.AddWithValue(":DMA_SUB_JML_PPNBM_11", form1111DM.A_Page.TotalPPnBM);
            cmd.Parameters.AddWithValue(":DMR_SUB_JML_DPP_11", form1111DM.R_Page.TotalDpp);
            cmd.Parameters.AddWithValue(":DMR_SUB_JML_PPN_11", form1111DM.R_Page.TotalPpn);
            cmd.Parameters.AddWithValue(":DMR_SUB_JML_PPNBM_11", form1111DM.R_Page.TotalPPnBM);
            cmd.Parameters.AddWithValue(":DOCID", form1111DM.DocId);

            cmd.ExecuteNonQuery();
        }

        private static void UpdateQAJob(Form1111DM form1111DM, string log, string originalLog, string statusAkhir, OracleCommand cmd)
        {
            string sql = @"UPDATE QA_JOB SET STATUSAKHIR=:STATUSAKHIR, 
                                        WAKTUPENGECEKAN=:WAKTUPENGECEKAN, CATATAN=:CATATAN
                                    WHERE DOCID=:DOCID";
            cmd.CommandText = sql;
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue(":STATUSAKHIR", statusAkhir);
            cmd.Parameters.AddWithValue(":WAKTUPENGECEKAN", DateTime.Now);
            StringBuilder sb = new StringBuilder(log + originalLog);
            if (sb.Length > 4000) sb.Remove(4000, sb.Length - 4000);
            cmd.Parameters.AddWithValue(":CATATAN", sb.ToString());
            cmd.Parameters.AddWithValue(":DOCID", form1111DM.DocId);
            cmd.ExecuteNonQuery();
        }

        internal static void UpdateQAJob(string log, string originalLog, string statusAkhir, string docId, string barcode)
        {
            using (OracleConnection con = new OracleConnection(Decrypt(ConfigurationManager.ConnectionStrings["QA"].ConnectionString)))
            {
                con.Open();
                using (OracleTransaction tx = con.BeginTransaction())
                {
                    OracleCommand cmd = new OracleCommand();
                    cmd.Connection = con;
                    cmd.Transaction = tx;

                    string sql = @"UPDATE QA_JOB SET STATUSAKHIR=:STATUSAKHIR, 
                                        WAKTUPENGECEKAN=:WAKTUPENGECEKAN, CATATAN=:CATATAN
                                    WHERE DOCID=:DOCID AND BARCODE=:BARCODE";
                    cmd.CommandText = sql;
                    cmd.Parameters.Clear();
                    cmd.Parameters.AddWithValue(":STATUSAKHIR", statusAkhir);
                    cmd.Parameters.AddWithValue(":WAKTUPENGECEKAN", DateTime.Now);
                    StringBuilder sb = new StringBuilder(log + originalLog);
                    if (sb.Length > 4000) sb.Remove(4000, sb.Length - 4000);
                    cmd.Parameters.AddWithValue(":CATATAN", sb.ToString());
                    cmd.Parameters.AddWithValue(":DOCID", docId);
                    cmd.Parameters.AddWithValue(":BARCODE", barcode);
                    cmd.ExecuteNonQuery();
                    tx.Commit();
                }
            }
        }

        private static void UpdateDMINDEX1(Form1111 form1111, OracleCommand cmd)
        {
            string sql = @"UPDATE PPNINDEX_11 SET 
                                  P_IA1_DPP_11=:P_IA1_DPP_11,
                                  P_IA2_DPP_11=:P_IA2_DPP_11,
                                  P_IA2_PPN_11=:P_IA2_PPN_11,
                                  P_IA3_DPP_11=:P_IA3_DPP_11,
                                  P_IA3_PPN_11=:P_IA3_PPN_11,
                                  P_IA4_DPP_11=:P_IA4_DPP_11,
                                  P_IA4_PPN_11=:P_IA4_PPN_11,
                                  P_IA5_DPP_11=:P_IA5_DPP_11,
                                  P_IA5_PPN_11=:P_IA5_PPN_11,
                                  P_IA_JML_DPP_11=:P_IA_JML_DPP_11,
                                  P_IA_JML_PPN_11=:P_IA_JML_PPN_11,
                                  P_IB_11=:P_IB_11,
                                  P_IC_JML_11=:P_IC_JML_11,
                                  P_IIA_11=:P_IIA_11,
                                  P_IIB_11=:P_IIB_11,
                                  P_IIC_11=:P_IIC_11,
                                  P_IID_11=:P_IID_11,
                                  P_IIE_11=:P_IIE_11,
                                  P_IIF_11=:P_IIF_11,
                                  P_IIIA_11=:P_IIIA_11,
                                  P_IIIB_11=:P_IIIB_11,
                                  P_IVA_11=:P_IVA_11,
                                  P_VA_11=:P_VA_11,
                                  P_VB_11=:P_VB_11,
                                  P_VC_11=:P_VC_11,
                                  P_VD_11=:P_VD_11,
                                  P_VE_11=:P_VE_11,
                                  A1_JML_DPP_11=:A1_JML_DPP_11,
                                  A2_JML_DPP_11=:A2_JML_DPP_11,
                                  A2_JML_PPN_11=:A2_JML_PPN_11,
                                  A2_JML_PPNBM_11=:A2_JML_PPNBM_11,
                                  B1_JML_DPP_11=:B1_JML_DPP_11,
                                  B1_JML_PPN_11=:B1_JML_PPN_11,
                                  B1_JML_PPNBM_11=:B1_JML_PPNBM_11,
                                  B2_JML_DPP_11=:B2_JML_DPP_11,
                                  B2_JML_PPN_11=:B2_JML_PPN_11,
                                  B2_JML_PPNBM_11=:B2_JML_PPNBM_11,
                                  B3_JML_DPP_11=:B3_JML_DPP_11,
                                  B3_JML_PPN_11=:B3_JML_PPN_11,
                                  B3_JML_PPNBM_11=:B3_JML_PPNBM_11,
                                  AB_IA_DPP_11=:AB_IA_DPP_11,
                                  AB_IB1_DPP_11=:AB_IB1_DPP_11,
                                  AB_IB1_PPN_11=:AB_IB1_PPN_11,
                                  AB_IB1_PPNBM_11=:AB_IB1_PPNBM_11,
                                  AB_IB2_DPP_11=:AB_IB2_DPP_11,
                                  AB_IB2_PPN_11=:AB_IB2_PPN_11,
                                  AB_IB2_PPNBM_11=:AB_IB2_PPNBM_11,
                                  AB_IC1_DPP_11=:AB_IC1_DPP_11,
                                  AB_IC1_PPN_11=:AB_IC1_PPN_11,
                                  AB_IC1_PPNBM_11=:AB_IC1_PPNBM_11,
                                  AB_IC2_DPP_11=:AB_IC2_DPP_11,
                                  AB_IC2_PPN_11=:AB_IC2_PPN_11,
                                  AB_IC2_PPNBM_11=:AB_IC2_PPNBM_11,
                                  AB_IC3_DPP_11=:AB_IC3_DPP_11,
                                  AB_IC3_PPN_11=:AB_IC3_PPN_11,
                                  AB_IC3_PPNBM_11=:AB_IC3_PPNBM_11,
                                  AB_IC4_DPP_11=:AB_IC4_DPP_11,
                                  AB_IC4_PPN_11=:AB_IC4_PPN_11,
                                  AB_IC4_PPNBM_11=:AB_IC4_PPNBM_11,
                                  AB_IIA_DPP_11=:AB_IIA_DPP_11,
                                  AB_IIA_PPN_11=:AB_IIA_PPN_11,
                                  AB_IIA_PPNBM_11=:AB_IIA_PPNBM_11,
                                  AB_IIB_DPP_11=:AB_IIB_DPP_11,
                                  AB_IIB_PPN_11=:AB_IIB_PPN_11,
                                  AB_IIB_PPNBM_11=:AB_IIB_PPNBM_11,
                                  AB_IIC_DPP_11=:AB_IIC_DPP_11,
                                  AB_IIC_PPN_11=:AB_IIC_PPN_11,
                                  AB_IIC_PPNBM_11=:AB_IIC_PPNBM_11,
                                  AB_IID_JML_DPP_11=:AB_IID_JML_DPP_11,
                                  AB_IID_JML_PPN_11=:AB_IID_JML_PPN_11,
                                  AB_IID_JML_PPNBM_11=:AB_IID_JML_PPNBM_11,
                                  AB_IIIA_PPN_11=:AB_IIIA_PPN_11,
                                  AB_IIIB1_PPN_11=:AB_IIIB1_PPN_11,
                                  AB_IIIB2_PPN_11=:AB_IIIB2_PPN_11,
                                  AB_IIIB3_PPN_11=:AB_IIIB3_PPN_11,
                                  AB_IIIB4_JML_PPN_11=:AB_IIIB4_JML_PPN_11,
                                  AB_IIIC_JML_PPN_11=:AB_IIIC_JML_PPN_11
                                  
                                    WHERE DOCID=:DOCID";
            cmd.CommandText = sql;
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue(":P_IA1_DPP_11", form1111.I_A_1);
            cmd.Parameters.AddWithValue(":P_IA2_DPP_11", form1111.I_A_2_DPP);
            cmd.Parameters.AddWithValue(":P_IA2_PPN_11", form1111.I_A_2_PPN);
            cmd.Parameters.AddWithValue(":P_IA3_DPP_11", form1111.I_A_3_DPP);
            cmd.Parameters.AddWithValue(":P_IA3_PPN_11", form1111.I_A_3_PPN);
            cmd.Parameters.AddWithValue(":P_IA4_DPP_11", form1111.I_A_4_DPP);
            cmd.Parameters.AddWithValue(":P_IA4_PPN_11", form1111.I_A_4_PPN);
            cmd.Parameters.AddWithValue(":P_IA5_DPP_11", form1111.I_A_5_DPP);
            cmd.Parameters.AddWithValue(":P_IA5_PPN_11", form1111.I_A_5_PPN);
            cmd.Parameters.AddWithValue(":P_IA_JML_DPP_11", form1111.I_A_Jml_DPP);
            cmd.Parameters.AddWithValue(":P_IA_JML_PPN_11", form1111.I_A_Jml_PPN);
            cmd.Parameters.AddWithValue(":P_IB_11", form1111.I_B);
            cmd.Parameters.AddWithValue(":P_IC_JML_11", form1111.I_C);
            cmd.Parameters.AddWithValue(":P_IIA_11", form1111.II_A);
            cmd.Parameters.AddWithValue(":P_IIB_11", form1111.II_B);
            cmd.Parameters.AddWithValue(":P_IIC_11", form1111.II_C);
            cmd.Parameters.AddWithValue(":P_IID_11", form1111.II_D);
            cmd.Parameters.AddWithValue(":P_IIE_11", form1111.II_E);
            cmd.Parameters.AddWithValue(":P_IIF_11", form1111.II_F);
            cmd.Parameters.AddWithValue(":P_IIIA_11", form1111.III_A);
            cmd.Parameters.AddWithValue(":P_IIIB_11", form1111.III_B);
            cmd.Parameters.AddWithValue(":P_IVA_11", form1111.IV_A);
            cmd.Parameters.AddWithValue(":P_VA_11", form1111.V_A);
            cmd.Parameters.AddWithValue(":P_VB_11", form1111.V_B);
            cmd.Parameters.AddWithValue(":P_VC_11", form1111.V_C);
            cmd.Parameters.AddWithValue(":P_VD_11", form1111.V_D);
            cmd.Parameters.AddWithValue(":P_VE_11", form1111.V_E);
            cmd.Parameters.AddWithValue(":A1_JML_DPP_11", form1111.A1_Page.TotalDpp);
            cmd.Parameters.AddWithValue(":A2_JML_DPP_11", form1111.A2_Page.TotalDpp);
            cmd.Parameters.AddWithValue(":A2_JML_PPN_11", form1111.A2_Page.TotalPpn);
            cmd.Parameters.AddWithValue(":A2_JML_PPNBM_11", form1111.A2_Page.TotalPPnBM);
            cmd.Parameters.AddWithValue(":B1_JML_DPP_11", form1111.B1_Page.TotalDpp);
            cmd.Parameters.AddWithValue(":B1_JML_PPN_11", form1111.B1_Page.TotalPpn);
            cmd.Parameters.AddWithValue(":B1_JML_PPNBM_11", form1111.B1_Page.TotalPPnBM);
            cmd.Parameters.AddWithValue(":B2_JML_DPP_11", form1111.B2_Page.TotalDpp);
            cmd.Parameters.AddWithValue(":B2_JML_PPN_11", form1111.B2_Page.TotalPpn);
            cmd.Parameters.AddWithValue(":B2_JML_PPNBM_11", form1111.B2_Page.TotalPPnBM);
            cmd.Parameters.AddWithValue(":B3_JML_DPP_11", form1111.B3_Page.TotalDpp);
            cmd.Parameters.AddWithValue(":B3_JML_PPN_11", form1111.B3_Page.TotalPpn);
            cmd.Parameters.AddWithValue(":B3_JML_PPNBM_11", form1111.B3_Page.TotalPPnBM);
            cmd.Parameters.AddWithValue(":AB_IA_DPP_11", form1111.I_A2);
            cmd.Parameters.AddWithValue(":AB_IB1_DPP_11", form1111.I_B2_1_DPP);
            cmd.Parameters.AddWithValue(":AB_IB1_PPN_11", form1111.I_B2_1_PPN);
            cmd.Parameters.AddWithValue(":AB_IB1_PPNBM_11", form1111.I_B2_1_PPnBM);
            cmd.Parameters.AddWithValue(":AB_IB2_DPP_11", form1111.I_B2_2_DPP);
            cmd.Parameters.AddWithValue(":AB_IB2_PPN_11", form1111.I_B2_2_PPN);
            cmd.Parameters.AddWithValue(":AB_IB2_PPNBM_11", form1111.I_B2_2_PPnBM);
            cmd.Parameters.AddWithValue(":AB_IC1_DPP_11", form1111.I_C2_1_DPP);
            cmd.Parameters.AddWithValue(":AB_IC1_PPN_11", form1111.I_C2_1_PPN);
            cmd.Parameters.AddWithValue(":AB_IC1_PPNBM_11", form1111.I_C2_1_PPnBM);
            cmd.Parameters.AddWithValue(":AB_IC2_DPP_11", form1111.I_C2_2_DPP);
            cmd.Parameters.AddWithValue(":AB_IC2_PPN_11", form1111.I_C2_2_PPN);
            cmd.Parameters.AddWithValue(":AB_IC2_PPNBM_11", form1111.I_C2_2_PPnBM);
            cmd.Parameters.AddWithValue(":AB_IC3_DPP_11", form1111.I_C2_3_DPP);
            cmd.Parameters.AddWithValue(":AB_IC3_PPN_11", form1111.I_C2_3_PPN);
            cmd.Parameters.AddWithValue(":AB_IC3_PPNBM_11", form1111.I_C2_3_PPnBM);
            cmd.Parameters.AddWithValue(":AB_IC4_DPP_11", form1111.I_C2_4_DPP);
            cmd.Parameters.AddWithValue(":AB_IC4_PPN_11", form1111.I_C2_4_PPN);
            cmd.Parameters.AddWithValue(":AB_IC4_PPNBM_11", form1111.I_C2_4_PPnBM);
            cmd.Parameters.AddWithValue(":AB_IIA_DPP_11", form1111.II_A2_DPP);
            cmd.Parameters.AddWithValue(":AB_IIA_PPN_11", form1111.II_A2_PPN);
            cmd.Parameters.AddWithValue(":AB_IIA_PPNBM_11", form1111.II_A2_PPnBM);
            cmd.Parameters.AddWithValue(":AB_IIB_DPP_11", form1111.II_B2_DPP);
            cmd.Parameters.AddWithValue(":AB_IIB_PPN_11", form1111.II_B2_PPN);
            cmd.Parameters.AddWithValue(":AB_IIB_PPNBM_11", form1111.II_B2_PPnBM);
            cmd.Parameters.AddWithValue(":AB_IIC_DPP_11", form1111.II_C2_DPP);
            cmd.Parameters.AddWithValue(":AB_IIC_PPN_11", form1111.II_C2_PPN);
            cmd.Parameters.AddWithValue(":AB_IIC_PPNBM_11", form1111.II_C2_PPnBM);
            cmd.Parameters.AddWithValue(":AB_IID_JML_DPP_11", form1111.II_D2_DPP);
            cmd.Parameters.AddWithValue(":AB_IID_JML_PPN_11", form1111.II_D2_PPN);
            cmd.Parameters.AddWithValue(":AB_IID_JML_PPNBM_11", form1111.II_D2_PPnBM);
            cmd.Parameters.AddWithValue(":AB_IIIA_PPN_11", form1111.III_A2);
            cmd.Parameters.AddWithValue(":AB_IIIB1_PPN_11", form1111.III_B2_1);
            cmd.Parameters.AddWithValue(":AB_IIIB2_PPN_11", form1111.III_B2_2);
            cmd.Parameters.AddWithValue(":AB_IIIB3_PPN_11", form1111.III_B2_3);
            cmd.Parameters.AddWithValue(":AB_IIIB4_JML_PPN_11", form1111.III_B2_4);
            cmd.Parameters.AddWithValue(":AB_IIIC_JML_PPN_11", form1111.III_C2);
            cmd.Parameters.AddWithValue(":DOCID", form1111.DocId);

            cmd.ExecuteNonQuery();
        }

        private static void UpdateQAJob1(Form1111 form1111, string log, string originalLog, string statusAkhir, OracleCommand cmd)
        {
            string sql = @"UPDATE QA_JOB SET STATUSAKHIR=:STATUSAKHIR, 
                                        WAKTUPENGECEKAN=:WAKTUPENGECEKAN, CATATAN=:CATATAN
                                    WHERE DOCID=:DOCID";
            cmd.CommandText = sql;
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue(":STATUSAKHIR", statusAkhir);
            cmd.Parameters.AddWithValue(":WAKTUPENGECEKAN", DateTime.Now);
            StringBuilder sb = new StringBuilder(log + originalLog);
            if (sb.Length > 4000) sb.Remove(4000, sb.Length - 4000);
            cmd.Parameters.AddWithValue(":CATATAN", sb.ToString());
            cmd.Parameters.AddWithValue(":DOCID", form1111.DocId);
            cmd.ExecuteNonQuery();
        }

        internal static QAJob GetJobByDocId(string docId)
        {
            using (OracleConnection con = new OracleConnection(Decrypt(ConfigurationManager.ConnectionStrings["QA"].ConnectionString)))
            {
                con.Open();
                string sql = @"SELECT DOCID, NAMAPTKP, NPWP, MASAPAJAK, WAKTUPENUGASAN, DITUGASKANKEPADA, 
                            STATUSASAL, STATUSAKHIR, WAKTUPENGECEKAN, CATATAN, JENISSPT, BARCODE FROM QA_JOB 
                            WHERE DOCID=:DOCID";

                using (OracleCommand cmd = new OracleCommand(sql, con))
                {
                    cmd.Parameters.AddWithValue(":DOCID", docId);
                    OracleDataReader reader = cmd.ExecuteReader();

                    if (reader.Read())
                    {
                        QAJob job = CreateQAJobObject(reader);
                        return job;
                    }
                    else
                        return null;
                }
            }
        }

        internal static QAJob GetJobByDocIdAndBarcode(string docId, string barcode)
        {
            using (OracleConnection con = new OracleConnection(Decrypt(ConfigurationManager.ConnectionStrings["QA"].ConnectionString)))
            {
                con.Open();
                string sql = @"SELECT DOCID, NAMAPTKP, NPWP, MASAPAJAK, WAKTUPENUGASAN, DITUGASKANKEPADA, 
                            STATUSASAL, STATUSAKHIR, WAKTUPENGECEKAN, CATATAN, JENISSPT, BARCODE FROM QA_JOB 
                            WHERE DOCID=:DOCID AND BARCODE=:BARCODE";

                using (OracleCommand cmd = new OracleCommand(sql, con))
                {
                    cmd.Parameters.AddWithValue(":DOCID", docId);
                    cmd.Parameters.AddWithValue(":BARCODE", barcode);
                    OracleDataReader reader = cmd.ExecuteReader();

                    if (reader.Read())
                    {
                        QAJob job = CreateQAJobObject(reader);
                        return job;
                    }
                    else
                        return null;
                }
            }
        }

        internal static bool IsValidLogin(string username)
        {
            using (OracleConnection con = new OracleConnection(Decrypt(ConfigurationManager.ConnectionStrings["QA"].ConnectionString)))
            {
                con.Open();
                string sql = @"SELECT COUNT(LOGINID) FROM QA_USER 
                            WHERE LOGINID=:LOGINID AND ISLOGIN=:ISLOGIN";

                using (OracleCommand cmd = new OracleCommand(sql, con))
                {
                    cmd.Parameters.AddWithValue(":LOGINID", username);
                    cmd.Parameters.AddWithValue(":ISLOGIN", false);
                    Decimal result = (Decimal)cmd.ExecuteScalar();

                    return result > 0;
                }
            }
        }

        internal static TipeSPT GetTipeSPTFromUser(string username)
        {
            using (OracleConnection con = new OracleConnection(Decrypt(ConfigurationManager.ConnectionStrings["QA"].ConnectionString)))
            {
                con.Open();
                string sql = @"SELECT USERGROUP FROM QA_USER 
                            WHERE LOGINID=:LOGINID";

                using (OracleCommand cmd = new OracleCommand(sql, con))
                {
                    cmd.Parameters.AddWithValue(":LOGINID", username);                    
                    OracleDataReader reader = cmd.ExecuteReader();

                    if (reader.Read())
                    {
                        if (reader["USERGROUP"].ToString().ToLower().Contains("unbalance"))
                            return TipeSPT.Unbalance;
                    }
                    return TipeSPT.Balance;
                }
            }
        }

        internal static void UpdateField(string fieldname, string fieldvalue, string table, string docid)
        {
            using (OracleConnection con = new OracleConnection(Decrypt(ConfigurationManager.ConnectionStrings["Datacap"].ConnectionString)))
            {
                con.Open();
                using (OracleTransaction tx = con.BeginTransaction())
                {
                    OracleCommand cmd = new OracleCommand();
                    cmd.Connection = con;
                    cmd.Transaction = tx;

                    string sql = @"UPDATE " + table + " SET " + fieldname + "=:FIELDVALUE WHERE DOCID=:DOCID";
                    cmd.CommandText = sql;
                    cmd.Parameters.Clear();
                    cmd.Parameters.AddWithValue(":FIELDVALUE", fieldvalue);
                    cmd.Parameters.AddWithValue(":DOCID", docid);
                    cmd.ExecuteNonQuery();
                    tx.Commit();
                }
            }
        }

        internal static void UbahStatusLoginPetugas(string username, int islogin)
        {
            using (OracleConnection con = new OracleConnection(Decrypt(ConfigurationManager.ConnectionStrings["QA"].ConnectionString)))
            {
                con.Open();
                string sql;
                sql = @"UPDATE QA_USER SET ISLOGIN=:ISLOGIN WHERE LOGINID=:LOGINID";

                using (OracleCommand cmd = new OracleCommand(sql, con))
                {
                    cmd.Parameters.AddWithValue(":ISLOGIN", islogin);
                    cmd.Parameters.AddWithValue(":LOGINID", username);
                    cmd.ExecuteNonQuery();
                }
            }
        }

        internal static void TambahQAChangeLog(QAChangeLog qa)
        {
            using (OracleConnection con = new OracleConnection(Decrypt(ConfigurationManager.ConnectionStrings["QA"].ConnectionString)))
            {
                con.Open();
                string sql = @"INSERT INTO QA_CHANGELOG (DOCID, BARCODE, FIELD_CHANGED, FIELD_VALUE, EDITOR, FIELD_ACTION, FIELD_TABLE, FIELD_BEFORE, COMMENTS)
                               VALUES(:DOCID, :BARCODE, :FIELD_CHANGED, :FIELD_VALUE, :EDITOR, :FIELD_ACTION, :FIELD_TABLE, :FIELD_BEFORE, :COMMENTS)";


                using (OracleTransaction tx = con.BeginTransaction())
                {
                    OracleCommand cmd = new OracleCommand(sql, con, tx);
                    cmd.Parameters.Clear();
                    cmd.Parameters.AddWithValue(":DOCID", qa.DocId);
                    cmd.Parameters.AddWithValue(":BARCODE", qa.Barcode);
                    cmd.Parameters.AddWithValue(":FIELD_CHANGED", qa.FieldChanged);
                    cmd.Parameters.AddWithValue(":FIELD_VALUE", qa.FieldValue);
                    cmd.Parameters.AddWithValue(":FIELD_ACTION", qa.FieldAction);
                    cmd.Parameters.AddWithValue(":FIELD_TABLE", qa.FieldTable);
                    cmd.Parameters.AddWithValue(":EDITOR", qa.Editor);
                    cmd.Parameters.AddWithValue(":FIELD_BEFORE", qa.FieldBefore);
                    cmd.Parameters.AddWithValue(":COMMENTS", qa.Comments);
                    cmd.ExecuteNonQuery();
                    tx.Commit();
                }
            }
        }        

        internal static void TambahField(string[] fieldname, string[] fieldvalue, string table, string docid, string tableNo)
        {
            string sFieldParam = string.Empty;
            string sFieldName = string.Empty;

            sFieldName = StringUtil.ConvertToString(fieldname);
            sFieldParam = StringUtil.ConvertToParamString(fieldvalue);

            using (OracleConnection con = new OracleConnection(Decrypt(ConfigurationManager.ConnectionStrings["Datacap"].ConnectionString)))
            {
                con.Open();
                string sql = string.Empty;
                if (!string.IsNullOrEmpty(tableNo))
                {
                    sql = @"INSERT INTO " + table + " (" + sFieldName + ",DOCID,TABLENO) ";
                    sql += @"VALUES(" + sFieldParam + ",:DOCID,:TABLENO)";
                }
                else
                {
                    sql = @"INSERT INTO " + table + " (" + sFieldName + ",DOCID) ";
                    sql += @"VALUES(" + sFieldParam + ",:DOCID)";
                }                                   

                using (OracleTransaction tx = con.BeginTransaction())
                {
                    OracleCommand cmd = new OracleCommand(sql, con, tx);
                    cmd.Parameters.Clear();
                    for (int i = 0; i < fieldvalue.Length; i++)
                    {
                        cmd.Parameters.AddWithValue(":P" + i, fieldvalue[i]);
                    }
                    cmd.Parameters.AddWithValue(":DOCID", docid);
                    if (!string.IsNullOrEmpty(tableNo))
                    {
                        cmd.Parameters.AddWithValue(":TABLENO", tableNo);
                    }
                    cmd.ExecuteNonQuery();
                    tx.Commit();
                }
            }

        }

        internal static void HapusField(string[] fieldname, string[] fieldvalue, string table, string docid)
        {
            string sFieldParam = string.Empty;
            string sFieldName = string.Empty;

            sFieldParam = StringUtil.ConvertToCompareParamString(fieldname, "=");

            using (OracleConnection con = new OracleConnection(Decrypt(ConfigurationManager.ConnectionStrings["Datacap"].ConnectionString)))
            {
                con.Open();
                string sql = @"DELETE FROM " + table;
                sql += @" WHERE (" + sFieldParam + " AND DOCID = :DOCID)";

                using (OracleTransaction tx = con.BeginTransaction())
                {
                    OracleCommand cmd = new OracleCommand(sql, con, tx);
                    cmd.Parameters.Clear();
                    for (int i = 0; i < fieldvalue.Length; i++)
                    {
                        cmd.Parameters.AddWithValue(":P" + i, fieldvalue[i]);
                    }
                    cmd.Parameters.AddWithValue(":DOCID", docid);
                    cmd.ExecuteNonQuery();
                    tx.Commit();
                }
            }

        }

        internal static void EditField(string[] fieldname, string[] fieldvalue, string table, string docid, string id)
        {
            string sFieldParam = string.Empty;
            string sFieldName = string.Empty;

            sFieldParam = StringUtil.ConvertToCompareParamString(fieldname, "=", ", ");

            using (OracleConnection con = new OracleConnection(Decrypt(ConfigurationManager.ConnectionStrings["Datacap"].ConnectionString)))
            {
                con.Open();
                string sql = @"UPDATE " + table;
                sql += @" SET " + sFieldParam + " WHERE DOCID = :DOCID AND ID = :ID";

                using (OracleTransaction tx = con.BeginTransaction())
                {
                    OracleCommand cmd = new OracleCommand(sql, con, tx);
                    cmd.Parameters.Clear();
                    for (int i = 0; i < fieldvalue.Length; i++)
                    {
                        cmd.Parameters.AddWithValue(":P" + i, fieldvalue[i]);
                    }
                    cmd.Parameters.AddWithValue(":DOCID", docid);
                    cmd.Parameters.AddWithValue(":ID", id);
                    cmd.ExecuteNonQuery();
                    tx.Commit();
                }
            }

        }

        internal static int GetDocIDFromStellent(string barcode, string npwp, string namawp)
        {
            int ret = -1;
            using (OracleConnection con = new OracleConnection(Decrypt(ConfigurationManager.ConnectionStrings["Stellent"].ConnectionString)))
            {
                con.Open();
                string sql = @"SELECT DID FROM DOCMETA 
                            WHERE XBARCODE=:BARCODE AND XNPWP=:NPWP AND XNAMAWP=:NAMAWP";

                using (OracleCommand cmd = new OracleCommand(sql, con))
                {
                    cmd.Parameters.AddWithValue(":BARCODE", barcode);
                    cmd.Parameters.AddWithValue(":NPWP", npwp);
                    cmd.Parameters.AddWithValue(":NAMAWP", namawp);
                    OracleDataReader reader = cmd.ExecuteReader();
                    if (reader.Read())
                    {
                        ret = reader.GetInt32(0);
                    }
                }

            }
            return ret;
        }

        internal static string Decrypt(string connectionString)
        {

            if (IsDecrypted(connectionString))
            {
                if (configFile.StartsWith(URI_PREFIX))
                {
                    LogUtil.WriteLog("Converting URI to absolute path");
                    configFile = configFile.Replace(URI_PREFIX, "");
                    configFile = configFile.Replace(URI_PATH_SEPARATOR, ABSOLUTE_PATH_SEPARATOR);
                    Util.LogUtil.WriteLog("\t" + configFile);
                }
                LogUtil.WriteLog("Encrypting connection string in the configuration file");
                string chiperText = CryptoHelper.Encrypt(connectionString);
                StringBuilder sb = new StringBuilder(File.ReadAllText(configFile));
                sb.Replace(connectionString, chiperText);
                File.WriteAllText(configFile, sb.ToString());
                return connectionString;
            }
            else
            {
                LogUtil.WriteLog("Decrypting connection string from the configuration file");
                return CryptoHelper.Decrypt(connectionString);
            }
        }

        private static bool IsDecrypted(string connectionString)
        {
            string upper = connectionString.ToUpper();
            return upper.Contains("PROVIDER") ||
                upper.Contains("DATA SOURCE") ||
                upper.Contains("USER ID");
        }

        //added by Vincent for validation login to qa sampling not for admin and supervisor 20130201
        internal static bool IsValidSupervisor(string username)
        {
            using (OracleConnection con = new OracleConnection(Decrypt(ConfigurationManager.ConnectionStrings["QA"].ConnectionString)))
            {
                con.Open();
                string sql = @"SELECT COUNT(LOGINID) FROM QA_USER 
                            WHERE LOGINID=:LOGINID AND (USERGROUP LIKE 'supervisor%')";

                using (OracleCommand cmd = new OracleCommand(sql, con))
                {
                    cmd.Parameters.AddWithValue(":LOGINID", username);
                    Decimal result = (Decimal)cmd.ExecuteScalar();

                    return result > 0;
                }
            }
        }

        internal static bool IsValidAdminLogin(string username)
        {
            using (OracleConnection con = new OracleConnection(Decrypt(ConfigurationManager.ConnectionStrings["QA"].ConnectionString)))
            {
                con.Open();
                string sql = @"SELECT COUNT(LOGINID) FROM QA_USER 
                            WHERE LOGINID=:LOGINID AND (USERGROUP='administrator')";

                using (OracleCommand cmd = new OracleCommand(sql, con))
                {
                    cmd.Parameters.AddWithValue(":LOGINID", username);
                    Decimal result = (Decimal)cmd.ExecuteScalar();

                    return result > 0;
                }
            }
        }
        //-------------------------------------------------------------------------------------------------------------------------------
    }
}
